﻿using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Reflection;
using System.Linq;
using System.Text;
using Microsoft.Extensions.Logging;

namespace NetCore.ORM.EF.Data
{
    public class SqlServer: AbstractDbContext
    {
        public SqlServer():base()
        {

        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">链接字符串</param>
        public SqlServer(string connectionString) : base(connectionString)
        {
        }

        public override ProviderType CurrentProviderType { get => ProviderType.SqlServer; }

        protected override void OnConfiguring(DbContextOptionsBuilder builder)
        {
            var loggerFactory = new LoggerFactory();
            loggerFactory.AddProvider(new EFLoggerProvider());
            builder.UseLoggerFactory(loggerFactory);
            builder.UseSqlServer(ApplicationEnvironments.Site.ConnectionString);
        }
        #region public string GetDBNow()
        /// <summary>
        /// 获得数据库日期时间
        /// </summary>
        /// <returns>日期时间</returns>
        public string GetDBNow()
        {
            return " Getdate() ";
        }
        #endregion

        #region public string GetDBDateTime()
        /// <summary>
        /// 获得数据库日期时间
        /// </summary>
        /// <returns>日期时间</returns>
        public string GetDBDateTime()
        {
            string commandText = " SELECT " + this.GetDBNow();
            this.Open();
            string dateTime = this.ExecuteScalar(CommandType.Text, commandText, new DbParameter[0]).ToString();
            this.Close();
            return dateTime;
        }
        #endregion


        #region public string SqlSafe(string value) 检查参数的安全性
        /// <summary>
        /// 检查参数的安全性
        /// </summary>
        /// <param name="value">参数</param>
        /// <returns>安全的参数</returns>
        public string SqlSafe(string value)
        {
            value = value.Replace("'", "''");
            // value = value.Replace("%", "'%");
            return value;
        }
        #endregion

        #region string PlusSign(params string[] values)
        /// <summary>
        ///  获得Sql字符串相加符号
        /// </summary>
        /// <param name="values">参数值</param>
        /// <returns>字符加</returns>
        public string PlusSign(params string[] values)
        {
            string returnValue = string.Empty;
            for (int i = 0; i < values.Length; i++)
            {
                returnValue += values[i] + " + ";
            }
            if (!String.IsNullOrEmpty(returnValue))
            {
                returnValue = returnValue.Substring(0, returnValue.Length - 3);
            }
            else
            {
                returnValue = " + ";
            }
            return returnValue;
        }
        #endregion


        #region public string GetParameter(string parameter) 获得参数Sql表达式
        /// <summary>
        /// 获得参数Sql表达式
        /// </summary>
        /// <param name="parameter">参数名称</param>
        /// <returns>字符串</returns>
        public override string GetParameter(string parameter)
        {
            return "@" + parameter + "";
        }


        /// <summary>
        /// 获得参数like Sql表达式
        /// </summary>
        /// <param name="parameter">参数名称</param>
        /// <returns>字符串</returns>
        public override string GetLikeParameter(string parameter)
        {
            return " '%'+@" + parameter + "+'%'";
        }
        #endregion

        #region public DbParameter MakeInParam(string targetFiled, object targetValue)
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="targetFiled">目标字段</param>
        /// <param name="targetValue">值</param>
        /// <returns>参数</returns>
        public DbParameter MakeInParam(string targetFiled, object targetValue)
        {
            return new SqlParameter("@" + targetFiled, targetValue);
        }
        #endregion

        #region public DbParameter[] MakeParameters(string targetFiled, object targetValue)
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="targetFiled">目标字段</param>
        /// <param name="targetValue">值</param>
        /// <returns>参数集</returns>
        public DbParameter[] MakeParameters(string targetFiled, object targetValue)
        {
            DbParameter[] dbParameters = null;
            if (targetFiled != null && targetValue != null)
            {
                dbParameters = new DbParameter[1];
                dbParameters[0] = this.MakeInParam(targetFiled, targetValue);
            }
            return dbParameters;
        }
        #endregion

        #region public DbParameter[] MakeParameters(string[] targetFileds, Object[] targetValues)
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="targetFiled">目标字段</param>
        /// <param name="targetValue">值</param>
        /// <returns>参数集</returns>
        public DbParameter[] MakeParameters(string[] targetFileds, Object[] targetValues)
        {
            // 这里需要用泛型列表，因为有不合法的数组的时候
            List<DbParameter> dbParameters = new List<DbParameter>();
            if (targetFileds != null && targetValues != null)
            {
                for (int i = 0; i < targetFileds.Length; i++)
                {
                    if (targetFileds[i] != null && targetValues[i] != null && (!(targetValues[i] is Array)))
                    {
                        dbParameters.Add(this.MakeInParam(targetFileds[i], targetValues[i]));
                    }
                }
            }
            return dbParameters.ToArray();
        }
        #endregion

        public override DbParameter MakeOutParam(string paramName, DbType DbType, int Size)
        {
            return MakeParam(paramName, DbType, Size, ParameterDirection.Output, null);
        }

        public override DbParameter MakeInParam(string paramName, DbType DbType, int Size, object Value)
        {
            return MakeParam(paramName, DbType, Size, ParameterDirection.Input, Value);
        }

        public override DbParameter MakeParam(string paramName, DbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            SqlParameter param;

            if (Size > 0)
            {
                param = new SqlParameter(paramName, GetDbType(DbType), Size);
            }
            else
            {
                param = new SqlParameter(paramName, GetDbType(DbType));
            }

            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;

            return param;
        }

        #region public void SqlBulkCopyData(DataTable dataTable)
        /// <summary>
        /// 利用Net SqlBulkCopy 批量导入数据库,速度超快
        /// </summary>
        /// <param name="dataTable">源内存数据表</param>
        public override void SqlBulkCopyData(DataTable dt)
        {
            using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(this.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction))//引用SqlBulkCopy  
            {
                try
                {
                    sqlRevdBulkCopy.DestinationTableName = dt.TableName;//数据库中对应的表名  
                    sqlRevdBulkCopy.NotifyAfter = dt.Rows.Count;//有几行数据  
                    foreach (DataColumn column in dt.Columns)
                    {
                        sqlRevdBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                    }
                    sqlRevdBulkCopy.WriteToServer(dt);//数据导入数据库  
                }
                catch (Exception)
                {

                }
                finally
                {
                    sqlRevdBulkCopy.Close();//关闭连接  
                }
            }
        }
        #endregion

        #region
       
        public override int SelectIdentity(string tableName)
        {
            object resultValue = base.ExecuteScalar(CommandType.Text, "select @@IDENTITY", null);
            return resultValue.ToInt();
        }
        public override DateTime Now()
        {
            object obj = base.ExecuteScalar("select getdate()");
            return ConvertHelper.ToDateTime(obj);
        }
        public override DbParameter[] GetParameter(List<DataParameter> parameters)
        {
            if (parameters != null && parameters.Count > 0)
            {
                DbParameter[] parameter = new DbParameter[parameters.Count];
                var index = 0;
                foreach (var item in parameters)
                {
                    parameter[index] = this.MakeParam(item.ParameterName, item.DbType, item.Size, item.Direction, item.Value);
                    index++;

                }
                return parameter;
            }
            return null;
        }

        public override DbParameter GetParameter(string name, object value)
        {
            return this.MakeInParam(name, value);
        }

        private SqlDbType GetDbType(DbType type)
        {
            switch (type)
            {
                case DbType.Binary:
                    return SqlDbType.Binary;

                case DbType.Guid:
                    return SqlDbType.UniqueIdentifier;

                case DbType.Int32:
                    return SqlDbType.Int;

                case DbType.Int16:
                    return SqlDbType.SmallInt;

                case DbType.Int64:
                    return SqlDbType.BigInt;

                case DbType.DateTime:
                    return SqlDbType.DateTime;

                case DbType.Double:
                    return SqlDbType.Decimal;

                case DbType.Decimal:
                    return SqlDbType.Decimal;

                case DbType.Byte:
                    return SqlDbType.Binary;
                case DbType.Single:
                    return SqlDbType.Decimal;

                case DbType.Boolean:
                    return SqlDbType.Bit;

                default:
                    return SqlDbType.NVarChar;

            }


        }

        public override string Identity(string where)
        {
            return " output inserted." + where;
        }

        #endregion

        /// <summary>
        /// 禁用自增
        /// </summary>
        /// <returns></returns>
        public override string Identity_Disabled(string tableName)
        {
            return "SET IDENTITY_INSERT [" + tableName + "] ON";
        }
        /// <summary>
        /// 启用自增
        /// </summary>
        /// <returns></returns>
        public override string Identity_Enable(string tableName)
        {
            return "SET IDENTITY_INSERT [" + tableName + "] ON";
        }


        public override string QueryPage(DbFactoryHelper action, Order[] orders,
        int pageIndex, int pageSize)
        {
            int fristIndex = (pageIndex - 1) * pageSize;
            int maxIndex = pageIndex * pageSize;
            string strOrder = "";
            if (orders != null && orders.Length > 0)
            {
                int index = 0;
                foreach (var order in orders)
                {
                    if (order == null)
                        continue;
                    if (index >= 1)
                        strOrder += ",";
                    else
                        strOrder = " order by ";
                    index++;
                    strOrder += order.ToString();
                }
            }
            if (ApplicationEnvironments.Site.ProviderVersion < 2012)
            {
                if (pageIndex > 0)
                {
                    fristIndex = fristIndex + 1;
                    return "select " + action.EntityMap.StrFieldBuilder + " from ( select ROW_NUMBER() over(" + strOrder + ") as RowIndex, " + action.EntityMap.StrFieldBuilder + " from " + action.EntityMap.TableName + " where 1=1 {0} ) as tb  where RowIndex between " + fristIndex + " and  " + maxIndex ;
                }
                else {
                    return " select " + action.EntityMap.StrFieldBuilder + " from " + action.EntityMap.TableName + " where 1=1 {0} ";
                }
              
            }
            else
            {
                return "select " + action.EntityMap.StrFieldBuilder + " from " + action.EntityMap.TableName + " where 1=1 {0} " + strOrder + (pageIndex > 0 ? " offset " + fristIndex + " rows fetch next " + pageSize + " rows only " : " ");
            }
        }
        public override string QueryPage(string hql, Order[] orders,
            int pageIndex, int pageSize)
        {
            StringBuilder strsql = new StringBuilder();
            if (ApplicationEnvironments.Site.ProviderVersion >= 2012)
            {
                strsql.Append(" " + hql + " {0} ");

            }
            string strOrder = "";
            int fristIndex = (pageIndex - 1) * pageSize;
            int maxIndex = pageIndex * pageSize;
            if (orders != null && orders.Length > 0)
            {
                int index = 0;
                foreach (var order in orders)
                {
                    if (order == null)
                    { continue; }
                    if (index >= 1)
                        strOrder += ",";
                    else
                        strOrder = " order by ";
                    index++;
                    strOrder += order.ToString();
                }
            }
            if (ApplicationEnvironments.Site.ProviderVersion < 2012)
            {
                if (pageIndex > 0)
                {
                    fristIndex = fristIndex + 1;
                    hql = "select ROW_NUMBER() over(" + strOrder + ") as RowIndex," + hql.Substring(hql.IndexOf("select ") + 7);
                    return " select * from ( " + hql + " {0}) as tb " + (pageIndex > 0 ? " where RowIndex between " + fristIndex + " and  " + maxIndex : " ") + "  ";
                }
                else
                {
                    return  hql + " {0} ";
                }
            }
            else
            {
                strsql.Append(strOrder + (pageIndex > 0 ? " offset " + fristIndex + " rows fetch next " + maxIndex + " rows only " : " "));
            }
            return strsql.ToString();
        }
        public override string RecordCount(DbFactoryHelper entity)
        {
            return " select count(1) from  " + entity.EntityMap.TableName + " where 1=1 {0}";
        }

        public override string RecordCount(string hql)
        {
            return " select count(1) from (" + hql + " {0}) as temptable";
        }

        public override string SelectAll(DbFactoryHelper entity)
        {
            return "select " + entity.EntityMap.StrFieldBuilder + " from " + entity.EntityMap.TableName + " where 1=1 " + ((entity.EntityMap.Table != null && entity.EntityMap.Table.IsDeleted) ? " and " + EntityBase.__IsDeleted + "=0 " : "") + " {0}";
        }

        #region idbsource

        public override string GetTable(string dbName)
        {
            return @" Select 0 as DbTableId,Name as TableName,Name as  DbTableCation,Name as  DbTableCationEn,case when XType='u' then 1 else 0 end as IsTable 
                 FROM [SysObjects] Where XType='U' orDER BY Name  ";
        }

        public override string GetView(string dbName)
        {
            return @" Select 0 as DbTableId,Name as TableName,Name as  DbTableCation,Name as  DbTableCationEn,case when XType='u' then 1 else 0 end as IsTable 
                 FROM [SysObjects] Where XType='V' orDER BY Name  ";
        }

        public override string GetDbColumn(string tableName, string serverName, bool IsTable)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append(@"
               
                SELECT  
                NNumber=a.colorder,   
                FieldName=a.name,
                IsIdentity=COLUMNPROPERTY(a.id,a.name,'IsIdentity'),   
                IsPrimary=case   when   exists(SELECT   1   FROM   [sysobjects]   where   xtype='PK'   and   name   in   (SELECT   name   FROM   [sysindexes]   WHERE   indid   in(SELECT   indid   FROM   [sysindexkeys]   WHERE   id   =   a.id   AND   colid=a.colid)))   then   1   else   0   end,   
                FieldType=b.name,   
                ByteLength=a.length,   
                NLength=COLUMNPROPERTY(a.id,a.name,'PRECISION'),   
                NScale=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),   
                NIsNull=a.isnullable,   
                DefaultValue=isnull(e.text,''),
                Comments = ' ' ,
                DbTypeName= b.name       
                FROM   [syscolumns]   a     
                left   join   [systypes]   b   on   a.xtype=b.xusertype     
                inner   join   [sysobjects]   d   on   a.id=d.id   and   d.xtype='" + (IsTable ? "U" : "V") + @"'   and   d.name<>'dtproperties'   
                left   join   [syscomments]  e   on   a.cdefault=e.id  
             --   LEFT OUTER JOIN   sys.extended_properties ex    on   ex.major_id = a.id AND ex.minor_id = a.colid  AND ex.name = 'MS_Description' 
             
                where   d.name='" + tableName + "' Order   by a.id,a.colorder");
            return strsql.ToString();
        }
        public override string GetSource(string tableName)
        {
            return "select * from " + tableName + " where 1=1";
        }

        public override string CreateTable(string tableName, List<DbColumnAttribute> dbColumns)
        {
            if (dbColumns == null || dbColumns.Count <= 0)
            {
                return "";
            }
           
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("CREATE TABLE "+ tableName);
            stringBuilder.Append("(");
            var index = 0;
            foreach (var item in dbColumns)
            {
                index++;
                if (index > 1)
                {
                    stringBuilder.Append(",");
                }
                string columnType = item.ColumnType;
                string defaultValue = item.Default;
                ConvertDbType(item.MaxLength, ref columnType, ref defaultValue);
                stringBuilder.Append("\r\n "+item.Name+ " " + columnType + " "+(item.Identifier? "identity(1,1) " : "")+ (item.IsPrimaryKey ? "primary key " : "") + (!item.AllowDBNull ? "not null ":"")+(!string.IsNullOrWhiteSpace(defaultValue) ? "default "+ defaultValue : ""));
            }
            stringBuilder.Append(")");
            return stringBuilder.ToString();
        }

        public override string AddColumn(string tableName, DbColumnAttribute columnItem)
        {
            StringBuilder stringBuilder = new StringBuilder();
            string columnType = columnItem.ColumnType;
            string defaultValue = columnItem.Default;
            ConvertDbType(columnItem.MaxLength, ref columnType, ref defaultValue);
            stringBuilder.Append("alter table "+ tableName + " add  " + columnItem.Name + " " + columnType + " " + (columnItem.Identifier ? "identity(1,1) " : "") + (columnItem.IsPrimaryKey ? "primary key " : "") + (!columnItem.AllowDBNull ? "not null " : "") + (!string.IsNullOrWhiteSpace(defaultValue) ? "default " + defaultValue : "") + ";");
            return stringBuilder.ToString();
        }

        /// <summary>
        /// 字段类型转换
        /// </summary>
        /// <param name="length"></param>
        /// <param name="columnType"></param>
        /// <param name="defaultValue"></param>
        public void ConvertDbType(int length,ref string columnType,ref string defaultValue)
        {
            columnType = columnType.ToLower();
            if (columnType.Equals("bit"))
            {
                if (!string.IsNullOrWhiteSpace(defaultValue))
                {
                    defaultValue = defaultValue.ToBool() ? "1" : "0";
                }
            }
            else if (columnType.Equals("datetime"))
            {
                if (!string.IsNullOrWhiteSpace(defaultValue))
                {
                    defaultValue = defaultValue.IndexOf("()")==-1 ? defaultValue+"()" : defaultValue;
                }
            }
            else if (columnType.IndexOf("char")!=-1)
            {
                columnType = columnType + "("+length.ToString()+")";
                if (!string.IsNullOrWhiteSpace(defaultValue))
                {
                    //defaultValue = defaultValue.ToBool() ? "1" : "0";
                }
            }
        }

        public override string DeleteTable(string table)
        {
            StringBuilder stringBuilder = new StringBuilder();
            return stringBuilder.ToString();
        }

        public override string DeleteColumn(string table, string columnName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            return stringBuilder.ToString();
        }
        #endregion
    }
}
